﻿IF EXISTS (SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'OTEntregaSearch')
	BEGIN
		DROP  Procedure  OTEntregaSearch
	END
GO


CREATE PROCEDURE [dbo].[OTEntregaSearch]
	-- Add the parameters for the stored procedure here
	@NumeroOT		VARCHAR(30) = NULL, 
	@EstadoId		INT = NULL,
	@FechaInicial	DATETIME = NULL,
	@FechaFinal		DATETIME = NULL,
	@EmpresaId		INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON

    -- Insert statements for procedure here
    IF (@FechaInicial IS NULL AND @FechaFinal IS NULL)    
		SELECT	ot.Id,
				ot.NumeroOT,
				oe.Orden AS Entrega,
				oe.Id AS EntregaId,
				oe.UnidadesSolicitadas,
				oe.FechaInicial, 
				oe.FechaFinal, 
				ot.ClienteId, 
				c.Cliente_Nombre as ClienteNombre, 
				ot.ArticuloId, 
				a.Art_Nombre as ArticuloNombre, 		
				ot.EstadoId,
				e.Estado
		FROM Lit_OT ot
		LEFT JOIN Cliente c ON ot.ClienteId = c.Cliente_Id AND ot.EmpresaId = c.Emp_Id
		LEFT JOIN Articulo a ON ot.ArticuloId = a.Art_Id AND ot.EmpresaId = a.Emp_Id
		INNER JOIN Lit_Estado e ON e.Id = ot.EstadoId
		INNER JOIN Lit_OTEntrega oe ON oe.OTId = ot.Id
		WHERE ot.NumeroOT = ISNULL(@NumeroOT, ot.NumeroOT)
		  AND ot.EstadoId = ISNULL(@EstadoId, ot.EstadoId)
		  AND ot.EmpresaId = @EmpresaId
	ELSE
		SELECT	ot.Id,
				ot.NumeroOT,
				oe.Orden AS Entrega,
				oe.Id AS EntregaId,
				oe.UnidadesSolicitadas,
				oe.FechaInicial, 
				oe.FechaFinal, 
				ot.ClienteId, 
				c.Cliente_Nombre as ClienteNombre, 
				ot.ArticuloId, 
				a.Art_Nombre as ArticuloNombre, 		
				ot.EstadoId,
				e.Estado
		FROM Lit_OT ot
		LEFT JOIN Cliente c ON ot.ClienteId = c.Cliente_Id AND ot.EmpresaId = c.Emp_Id
		LEFT JOIN Articulo a ON ot.ArticuloId = a.Art_Id AND ot.EmpresaId = a.Emp_Id
		INNER JOIN Lit_Estado e ON e.Id = ot.EstadoId
		INNER JOIN Lit_OTEntrega oe ON oe.OTId = ot.Id
		WHERE ot.NumeroOT = ISNULL(@NumeroOT, ot.NumeroOT)
		  AND ot.EstadoId = ISNULL(@EstadoId, ot.EstadoId)
		  AND ot.FechaInicial BETWEEN @FechaInicial AND @FechaFinal
		  AND ot.EmpresaId = @EmpresaId
END
GO
